﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;

namespace Having.Site.Data.Helper
{
    /// <summary>
    /// SQLite的操作
    /// </summary>
    public static class SQLiteHelper
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="Table"></param>
        /// <param name="PageNum"></param>
        /// <param name="PageSize"></param>
        /// <param name="OrderBy"></param>
        /// <param name="key"></param>
        /// <param name="Config"></param>
        /// <returns></returns>
        public static string  GetListSql(string Table, int PageNum, int PageSize, string OrderBy, string key, Config Config)
        {
            string sql = "";
            sql = "select * from (" + Table + ") as t_able  Order By " + OrderBy + " LIMIT "+ PageSize + " OFFSET " + (PageSize * (PageNum - 1));
            return sql;
        }
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="t"></param>
        /// <param name="dir"></param>
        /// <param name="idname"></param>
        /// <param name="key"></param>
        /// <param name="Config"></param>
        /// <returns></returns>
        public static int Add(object t, string[] dir, string idname, string key, Config Config)
        {
            PropertyInfo[] pis = t.GetType().GetProperties();
            List<DataItemType> list = new List<DataItemType>();
            string coms = "";
            string values = "";
            foreach (PropertyInfo pi in pis)
            {
                if (dir != null)
                {
                    if (dir.Contains(pi.Name))
                    {
                        continue;
                    }
                }
                if (pi.Name.ToLower() == idname.ToLower())
                {
                    list.Add(new DataItemType("@" + idname, ParameterDirection.Output, DbType.Int32, 8));
                }
                else
                {
                    var v = pi.GetValue(t, null);
                    list.Add(new DataItemType("@" + pi.Name, v));
                    coms += "[" + pi.Name + "],";
                    values += "@" + pi.Name + ",";
                }
            }
            coms = coms.Substring(0, coms.Length - 1);
            values = values.Substring(0, values.Length - 1);
            using (IData data = DataConnect.get(key, Config))
            {
                var sql = "insert into " + t.GetType().Name + "(" + coms + ") values(" + values + ");select  @" + idname + "=last_insert_rowid();";

                data.ExecuteNonQuery(sql.Replace("[", "").Replace("]", ""), list, CommandType.Text);
            }
            var op= Convert.ToInt32(list.Find(m => m.DataItemName == "@" + idname).DataItemValue);
            pis.Single(m => m.Name.ToLower() == idname.ToLower()).SetValue(t, op);
            return op;
        }

        /// <summary>
        /// 返回添加的sql
        /// </summary>
        /// <param name="t">对象类</param>
        /// <param name="list">参数集合（默认是null,sql不含参数）</param>
        /// <returns></returns>
        public static string AddSQLString(object t, List<DataItemType> list = null)
        {
            PropertyInfo[] pis = t.GetType().GetProperties();
            var coms = "";
            var values = "";
            foreach (PropertyInfo pi in pis)
            {
                var at = pi.GetCustomAttribute(typeof(Attr.AttKeyName));
                if (at != null && ((int)((Attr.AttKeyName)at).KeyType) > 1)
                {
                    continue;
                }
                else
                {
                    var v = pi.GetValue(t, null);
                    coms += "[" + pi.Name + "],";
                    if (list != null)
                    {
                        list.Add(new DataItemType("@" + pi.Name, v));
                        values += "@" + pi.Name + ",";
                    }
                    else
                    {
                        values += "'" + v.ToString().Replace("'", "\"") + "',";
                    }
                }

            }
            coms = coms.Substring(0, coms.Length == 0 ? 0 : (coms.Length - 1));
            values = values.Substring(0, values.Length == 0 ? 0 : (values.Length - 1));
            if (coms.Length > 0)
            {
                string sql = "insert into " + t.GetType().Name + " (" + coms + ") values (" + values + ");";
                return sql.Replace("[", "").Replace("]", "");
            }
            else
            {
                return "";
            }
        }
        /// <summary>
        /// 更新的Sql
        /// </summary>
        /// <param name="t"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public static string UpDateSQLString(object t, List<DataItemType> list = null)
        {
            PropertyInfo[] pis = t.GetType().GetProperties();
            var sets = "";
            var wheres = "";
            foreach (PropertyInfo pi in pis)
            {
                var pindex = 0;
                var at = pi.GetCustomAttribute(typeof(Attr.AttKeyName));
                if (at == null)
                {
                    pindex = 0;
                }
                else
                {
                    pindex = (int)((Attr.AttKeyName)at).KeyType;
                }

                var v = pi.GetValue(t, null);
                if (pi.PropertyType.IsEnum)
                {
                    v = (int)Convert.ChangeType(v, pi.PropertyType);
                }
                switch (pindex)
                {
                    case 1:
                    case 2:
                    case 3:
                        wheres += " " + pi.Name + "=";
                        if (list != null)
                        {
                            wheres += "@" + pi.Name + ",";
                            list.Add(new DataItemType("@" + pi.Name, v));
                        }
                        else
                        {
                            wheres += "'" + v.ToString().Replace("'", "\"") + "',";
                        }
                        break;
                    case 4:
                        break;
                    default:
                        sets += " " + pi.Name + "=";
                        if (list != null)
                        {
                            sets += "@" + pi.Name + ",";
                            list.Add(new DataItemType("@" + pi.Name, v));
                        }
                        else
                        {
                            sets += "'" + v.ToString().Replace("'", "\"") + "',";
                        }
                        break;
                }

            }
            sets = sets.Substring(0, sets.Length == 0 ? 0 : (sets.Length - 1));
            wheres = wheres.Substring(0, wheres.Length == 0 ? 0 : (wheres.Length - 1));
            if (sets.Length > 0)
            {
                string sql = "update  [" + t.GetType().Name + "] set " + sets + " ";
                if (wheres.Length > 0)
                {
                    sql += " where " + wheres;
                }
                return sql.Replace("[", "").Replace("]", "");
            }
            else
            {
                return "";
            }
        }

    }
}
